﻿/*
 * Created by SharpDevelop.
 * User: CrMosk
 * Date: 27.04.2013
 * Time: 9:34
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace Region.Utils
{
  /// <summary>
  /// Этот класс проверяет структуру базы данных
  /// </summary>
  public class DatabaseCheck
  {
    Engine m_engine;
    String m_DatabaseName;
    XDocument m_DBStruct;
    Dictionary<String,Dictionary<String,String>> Tables;
    Dictionary<String,Dictionary<String,String>> Columns;
    Dictionary<String,Dictionary<String,String>> Key_columns_usage;
    class SQLAction
    {
      public String SQL="";
      public SQLParam Param = null;
      public String Comment="";
      public SQLAction(String SQL, SQLParam Param, String Comment){this.SQL = SQL;this.Param=Param;this.Comment=Comment;}
    }
    List<SQLAction> Actions = new List<DatabaseCheck.SQLAction>();
    public DatabaseCheck(Engine engine)
    {
        m_engine = engine;
        m_DatabaseName = (String)m_engine.SQLSelect("SELECT DATABASE() AS DBNAME")[0]["DBNAME"];
        Byte[] DatabaseInfo = Compression.DecodeGZ(Region.RegionRes.DatabaseInfo);
        if (DatabaseInfo[0]==0xEF && DatabaseInfo[1]==0xBB && DatabaseInfo[2]==0xBF)
          m_DBStruct = XDocument.Parse(Encoding.UTF8.GetString(DatabaseInfo,3,Region.RegionRes.DatabaseInfo.Length-3));
        else
          m_DBStruct = XDocument.Parse(Encoding.UTF8.GetString(DatabaseInfo));        
        Tables = (from t in m_DBStruct.Root.Element("TABLES").Elements("TABLE") select t.Attributes().ToDictionary(K => K.Name.LocalName,V=>V.Value)).ToDictionary(K=>K["TABLE_NAME"]);
        Columns = (from t in m_DBStruct.Root.Element("COLUMNS").Elements("COLUMN") select t.Attributes().ToDictionary(K => K.Name.LocalName,V=>V.Value)).ToDictionary(K=>K["TABLE_NAME"]+"."+K["COLUMN_NAME"]);
        Key_columns_usage = (from t in m_DBStruct.Root.Element("KEY_COLUMN_USAGE").Elements("KEY_COLUMN") select t.Attributes().ToDictionary(K => K.Name.LocalName,V=>V.Value)).ToDictionary(K=>K["TABLE_NAME"]+"."+K["CONSTRAINT_NAME"]+"."+K["COLUMN_NAME"]);
        //m_DBStruct = new XDocument();        
        CheckTable();
        CheckColumn();
        
    }
    String genSQLCOLUMN(String Table,String Column,SQLParam Params)
    {
      String SQL="";            
      var C = Columns[Table+"."+Column];
      SQL+="`"+Column+"` "+C["COLUMN_TYPE"];      
      if (C["IS_NULLABLE"].ToUpper()=="NO") SQL+=" NOT NULL";
      if (C["EXTRA"].ToUpper()=="AUTO_INCREMENT") SQL +=" AUTO_INCREMENT";
      if (C["COLUMN_DEFAULT"].ToUpper()=="CURRENT_TIMESTAMP") SQL +=" DEFAULT CURRENT_TIMESTAMP";
      else if (C["COLUMN_DEFAULT"].ToUpper()!="NULL")
      {
        SQL +=" DEFAULT @"+Column+"_DEFAULT";
        Params["@"+Column+"_DEFAULT"] = C["COLUMN_DEFAULT"];
      }
      if (C["COLUMN_COMMENT"].Trim()!="") 
      {
        SQL +=" COMMENT @"+Column+"_COMMENT";
        Params["@"+Column+"_COMMENT"] = C["COLUMN_COMMENT"];
      }
      return SQL;
    }
    bool CheckColumn()
    {
      var  Column = m_engine.SQLSelect("SELECT TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,COLUMN_TYPE,COLUMN_KEY,EXTRA,COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@DBNAME",new SQLParam(){{"@DBNAME",m_DatabaseName}});
      var c = Column.ToDictionary(K=>K.Value["TABLE_NAME"].ToString()+"."+K.Value["COLUMN_NAME"].ToString(),V=>V.Value);
      Boolean Edit=false;
      foreach(var ColumnItem in Columns)
      {
        if (c.ContainsKey(ColumnItem.Key))
        {
          
          if (DBNull.Value.Equals(c[ColumnItem.Key]["COLUMN_DEFAULT"])) c[ColumnItem.Key]["COLUMN_DEFAULT"]="NULL";
          if (DBNull.Value.Equals(c[ColumnItem.Key]["IS_NULLABLE"])) c[ColumnItem.Key]["IS_NULLABLE"]="NULL";
          if (DBNull.Value.Equals(c[ColumnItem.Key]["COLUMN_TYPE"])) c[ColumnItem.Key]["COLUMN_TYPE"]="NULL";
          if (DBNull.Value.Equals(c[ColumnItem.Key]["COLUMN_KEY"])) c[ColumnItem.Key]["COLUMN_KEY"]="NULL";
          if (DBNull.Value.Equals(c[ColumnItem.Key]["EXTRA"])) c[ColumnItem.Key]["EXTRA"]="NULL";
          if (DBNull.Value.Equals(c[ColumnItem.Key]["COLUMN_COMMENT"])) c[ColumnItem.Key]["COLUMN_COMMENT"]="NULL";
          
          //ALTER TABLE `users` CHANGE `Name` `Name` CHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Имя пользователя2'
          if (c[ColumnItem.Key]["COLUMN_DEFAULT"].ToString() != ColumnItem.Value["COLUMN_DEFAULT"]||
              c[ColumnItem.Key]["IS_NULLABLE"].ToString() != ColumnItem.Value["IS_NULLABLE"]||
              c[ColumnItem.Key]["COLUMN_TYPE"].ToString() != ColumnItem.Value["COLUMN_TYPE"]||              
              c[ColumnItem.Key]["EXTRA"].ToString() != ColumnItem.Value["EXTRA"]||
              c[ColumnItem.Key]["COLUMN_COMMENT"].ToString() != ColumnItem.Value["COLUMN_COMMENT"]
             )
          {
            SQLParam Params = new SQLParam();          
            String SQL="ALTER TABLE `"+ColumnItem.Value["TABLE_NAME"]+"` CHANGE `"+ColumnItem.Value["COLUMN_NAME"]+"` "+genSQLCOLUMN(ColumnItem.Value["TABLE_NAME"],ColumnItem.Value["COLUMN_NAME"],Params);
            Actions.Add(new SQLAction(SQL,Params,"Изменение столбца "+ColumnItem.Value["COLUMN_NAME"]+" в таблицу "+ColumnItem.Value["TABLE_NAME"])); 
            Edit=true;
            m_engine.SQLEdit(SQL,Params);
          }
        }
        else
        {
          if ( ColumnItem.Value["COLUMN_KEY"]!="PRI")
          {
            SQLParam Params = new SQLParam();          
            String SQL="ALTER TABLE `"+ColumnItem.Value["TABLE_NAME"]+"` ADD "+genSQLCOLUMN(ColumnItem.Value["TABLE_NAME"],ColumnItem.Value["COLUMN_NAME"],Params);
            Edit=true;
            Actions.Add(new SQLAction(SQL,Params,"Вставка столбца "+ColumnItem.Value["COLUMN_NAME"]+" в таблицу "+ColumnItem.Value["TABLE_NAME"])); 
          }
        }
      }
      //<COLUMN TABLE_NAME="users" COLUMN_NAME="UserID"             COLUMN_DEFAULT="NULL"               IS_NULLABLE="NO" COLUMN_TYPE="int(11)"   COLUMN_KEY="PRI"  EXTRA="auto_increment"  COLUMN_COMMENT="Идентификатор пользователя" />
      //ALTER TABLE `users` ADD `POle1` INT NOT NULL 
      return !Edit;
    }
    /// <summary>
    /// Проверяет существует таблица если нет создает таблику с поями основного ключа
    /// </summary>
    /// <returns>true - все таблици существуют</returns>
    bool CheckTable()
    {
      var tables = m_engine.SQLSelect("SELECT TABLE_NAME,ENGINE,TABLE_COLLATION,TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=@DBNAME",new SQLParam(){{"@DBNAME",m_DatabaseName}});
      var t = tables.ToDictionary(K=>K.Value["TABLE_NAME"].ToString(),V=>V.Value);
      Boolean Edit=false;
      foreach(var TableItem in Tables)
      {
        if (t.ContainsKey(TableItem.Key))
        {
          String SQL = "";
          if (((String)t[TableItem.Key]["ENGINE"]).ToUpper()!=TableItem.Value["ENGINE"].ToUpper())
            SQL+=" ENGINE = InnoDB";
          if (((String)t[TableItem.Key]["TABLE_COLLATION"]).ToUpper()!=TableItem.Value["TABLE_COLLATION"].ToUpper())
            SQL+=" DEFAULT CHARACTER SET utf8 COLLATE "+(String)t[TableItem.Key]["TABLE_COLLATION"];
          if (SQL!="")
          {
            Edit=true;
            SQL="ALTER TABLE `"+TableItem.Key+"`"+SQL;
            Actions.Add(new SQLAction(SQL,null,"Вснесение изменений в таблицу "+TableItem.Key));            
          }
         
        }
        else
        {
          Dictionary<Int32,String> PrimaryKeyPoles= new Dictionary<Int32,String>();
          foreach(var c in Columns)
            if (c.Value["TABLE_NAME"]==TableItem.Key && c.Value["COLUMN_KEY"].ToUpper()=="PRI")
          {
            Int32 Order=-1;
            foreach(var kc in Key_columns_usage)
              if (kc.Value["TABLE_NAME"]==TableItem.Key && kc.Value["COLUMN_NAME"]==c.Value["COLUMN_NAME"])
                if (Int32.TryParse(kc.Value["ORDINAL_POSITION"],out Order))
                  break;
            PrimaryKeyPoles[Order] = c.Value["COLUMN_NAME"];
          }
          SQLParam Params = new SQLParam();
          String SQLTemplate = "CREATE TABLE IF NOT EXISTS `{0}` ({1}, PRIMARY KEY ({2})) ENGINE={3} DEFAULT CHARSET=utf8 COMMENT=@TABLE_COMMENT";
          //String SQLTemplate = "CREATE TABLE IF NOT EXISTS `{0}` ({1}, PRIMARY KEY ({2})) ENGINE={3} DEFAULT CHARSET=utf8 ";
          Params["@TABLE_COMMENT"] = TableItem.Value["TABLE_COMMENT"];
          String Poles="";
          String Primarykey="";
          for(int i=1;PrimaryKeyPoles.ContainsKey(i);i++)
          {
            if (Poles!="") Poles+=", ";
            Poles+=genSQLCOLUMN(TableItem.Key,PrimaryKeyPoles[i],Params);              
            if (Primarykey!="") Primarykey+=", ";
            Primarykey+="`"+PrimaryKeyPoles[i]+"`";
          }
          String SQL = String.Format(SQLTemplate,TableItem.Value["TABLE_NAME"],Poles,Primarykey,TableItem.Value["ENGINE"]);Edit=true;
          Edit=true;
          Actions.Add(new SQLAction(SQL,Params,"Вставка новой таблици "+TableItem.Key));
        }
      }
      return !Edit;
    }
  }
}
